import akshare as ak
from database_manager import get_db_connection
from stock_data_provider import process_stock_symbol

def get_stock_name(symbol):
    """获取股票名称"""
    # 提取纯股票代码（去掉交易所后缀）
    pure_symbol = symbol.split('.')[0]
    print(f"尝试获取股票名称: {pure_symbol}")
    
    # 1. 优先从数据库获取
    conn = get_db_connection()
    if conn:
        try:
            with conn.cursor() as cursor:
                # 使用标准化的股票代码进行查询
                cursor.execute("""
                    SELECT stock_name FROM stock_info 
                    WHERE symbol_standardized = %s 
                    LIMIT 1
                """, (pure_symbol,))
                result = cursor.fetchone()
                if result:
                    stock_name = result['stock_name']
                    print(f"从数据库获取股票名称: {pure_symbol} - {stock_name}")
                    return stock_name
        except Exception as e:
            print(f"从数据库获取股票名称失败: {e}")
        finally:
            conn.close()
    
    # 2. 备选方案：定义股票代码到名称的映射字典
    stock_name_mapping = {
        '600519': '贵州茅台',
        '000858': '五粮液',
        '002594': '比亚迪',
        '601318': '中国平安',
        '600036': '招商银行',
        '000002': '万科A',
        '600000': '浦发银行',
        '000725': '京东方A',
        '601166': '兴业银行',
        '601328': '交通银行'
    }
    
    # 从映射字典中获取
    if pure_symbol in stock_name_mapping:
        stock_name = stock_name_mapping[pure_symbol]
        print(f"从映射字典获取股票名称: {pure_symbol} - {stock_name}")
        # 同时更新到数据库
        update_stock_info(pure_symbol, stock_name)
        return stock_name
    
    # 3. 方案1：使用stock_individual_info_em获取个股信息
    try:
        try:
            stock_info = ak.stock_individual_info_em(symbol=pure_symbol)
            if not stock_info.empty and 'value' in stock_info.columns:
                # 查找股票名称行
                name_row = stock_info[stock_info['item'] == '股票名称']
                if not name_row.empty:
                    stock_name = name_row.iloc[0]['value']
                    print(f"成功获取股票名称: {pure_symbol} - {stock_name}")
                    # 更新到数据库
                    update_stock_info(pure_symbol, stock_name)
                    return stock_name
        except:
            # 方案2：使用stock_zh_a_spot获取实时行情
            try:
                stock_spot_df = ak.stock_zh_a_spot()
                # 查找对应的股票名称
                stock_info = stock_spot_df[stock_spot_df['代码'] == pure_symbol]
                if not stock_info.empty:
                    # 尝试不同的列名
                    for col in ['名称', 'name']:
                        if col in stock_info.columns:
                            stock_name = stock_info.iloc[0][col]
                            print(f"成功获取股票名称: {pure_symbol} - {stock_name}")
                            # 更新到数据库
                            update_stock_info(pure_symbol, stock_name)
                            return stock_name
            except:
                # 方案3：使用stock_basic_info_sina获取基本信息
                try:
                    stock_info = ak.stock_basic_info_sina(symbol=pure_symbol)
                    if isinstance(stock_info, dict) and 'name' in stock_info:
                        stock_name = stock_info['name']
                        print(f"成功获取股票名称: {pure_symbol} - {stock_name}")
                        # 更新到数据库
                        update_stock_info(pure_symbol, stock_name)
                        return stock_name
                except:
                    pass
        
        print(f"未找到股票名称: {pure_symbol}")
        return None
    except Exception as e:
        error_msg = str(e)
        print(f"获取股票名称错误: {error_msg}")
        # 如果获取失败，返回None，不影响主要功能
        return None

def get_stock_industry(symbol):
    """获取股票行业信息"""
    # 提取纯股票代码（去掉交易所后缀）
    pure_symbol = symbol.split('.')[0]
    print(f"尝试获取股票行业信息: {pure_symbol}")
    
    # 1. 优先从数据库获取
    conn = get_db_connection()
    if conn:
        try:
            with conn.cursor() as cursor:
                # 使用标准化的股票代码进行查询
                cursor.execute("""
                    SELECT industry FROM stock_info 
                    WHERE symbol_standardized = %s 
                    LIMIT 1
                """, (pure_symbol,))
                result = cursor.fetchone()
                if result and result['industry']:
                    industry = result['industry']
                    print(f"从数据库获取股票行业: {pure_symbol} - {industry}")
                    return industry
        except Exception as e:
            print(f"从数据库获取股票行业失败: {e}")
        finally:
            conn.close()
    
    # 2. 备选方案：定义股票代码到行业的映射字典
    stock_industry_mapping = {
        '600519': '白酒',
        '000858': '白酒',
        '002594': '新能源汽车',
        '601318': '保险',
        '600036': '银行',
        '000002': '房地产',
        '600000': '银行',
        '000725': '电子',
        '601166': '银行',
        '601328': '银行'
    }
    
    # 从映射字典中获取
    if pure_symbol in stock_industry_mapping:
        industry = stock_industry_mapping[pure_symbol]
        print(f"从映射字典获取股票行业: {pure_symbol} - {industry}")
        # 同时更新到数据库
        update_stock_info(pure_symbol, industry=industry)
        return industry
    
    # 3. 尝试多种方式获取行业信息
    try:
        # 方案1：使用stock_individual_info_em获取行业信息
        try:
            stock_info = ak.stock_individual_info_em(symbol=pure_symbol)
            if not stock_info.empty and 'value' in stock_info.columns:
                # 查找行业相关行
                for item_name in ['所属行业', '行业']:
                    industry_row = stock_info[stock_info['item'] == item_name]
                    if not industry_row.empty:
                        industry = industry_row.iloc[0]['value']
                        print(f"成功获取股票行业信息: {pure_symbol} - {industry}")
                        # 更新到数据库
                        update_stock_info(pure_symbol, industry=industry)
                        return industry
        except:
            # 方案2：尝试从实时行情中获取行业信息
            try:
                stock_spot_df = ak.stock_zh_a_spot()
                stock_info = stock_spot_df[stock_spot_df['代码'] == pure_symbol]
                if not stock_info.empty:
                    # 尝试获取行业列（不同版本的akshare列名可能不同）
                    for col in ['所属行业', 'industry', '行业']:
                        if col in stock_info.columns:
                            industry = stock_info.iloc[0][col]
                            print(f"成功从实时行情获取股票行业信息: {pure_symbol} - {industry}")
                            # 更新到数据库
                            update_stock_info(pure_symbol, industry=industry)
                            return industry
            except:
                # 方案3：尝试申万行业分类
                try:
                    industry_df = ak.stock_board_industry_name_ths(symbol=pure_symbol)
                    if not industry_df.empty:
                        # 尝试不同的列名
                        for col in ['industry_name', '行业名称']:
                            if col in industry_df.columns:
                                industry = industry_df.iloc[0][col]
                                print(f"成功获取股票行业信息: {pure_symbol} - {industry}")
                                # 更新到数据库
                                update_stock_info(pure_symbol, industry=industry)
                                return industry
                except:
                    # 方案4：尝试东财行业分类
                    try:
                        industry_df = ak.stock_board_industry_name_em(symbol=pure_symbol)
                        if not industry_df.empty:
                            # 尝试不同的列名
                            for col in ['industry_name', '行业名称']:
                                if col in industry_df.columns:
                                    industry = industry_df.iloc[0][col]
                                    print(f"成功获取股票行业信息(东财): {pure_symbol} - {industry}")
                                    # 更新到数据库
                                    update_stock_info(pure_symbol, industry=industry)
                                    return industry
                    except:
                        pass
        
        print(f"未找到股票行业信息: {pure_symbol}")
        return None
    except Exception as e:
        error_msg = str(e)
        print(f"获取股票行业信息错误: {error_msg}")
        # 如果获取失败，返回None，不影响主要功能
        return None

def update_stock_info(symbol_standardized, stock_name=None, industry=None):
    """更新股票信息到数据库"""
    conn = get_db_connection()
    if not conn:
        return False
    
    try:
        with conn.cursor() as cursor:
            # 检查是否已存在记录
            cursor.execute("""
                SELECT id FROM stock_info 
                WHERE symbol_standardized = %s 
                LIMIT 1
            """, (symbol_standardized,))
            existing = cursor.fetchone()
            
            if existing:
                # 更新现有记录
                updates = []
                params = []
                
                if stock_name:
                    updates.append("stock_name = %s")
                    params.append(stock_name)
                if industry:
                    updates.append("industry = %s")
                    params.append(industry)
                
                if updates:
                    params.append(existing['id'])
                    sql = f"UPDATE stock_info SET {', '.join(updates)} WHERE id = %s"
                    cursor.execute(sql, params)
            else:
                # 插入新记录
                symbol = symbol_standardized
                # 根据规则添加交易所后缀
                if len(symbol_standardized) == 6:
                    if symbol_standardized.startswith('6'):
                        symbol += '.SS'  # 上交所
                    elif symbol_standardized.startswith('0') or symbol_standardized.startswith('3'):
                        symbol += '.SZ'  # 深交所
                
                cursor.execute("""
                    INSERT INTO stock_info (symbol, symbol_standardized, stock_name, industry)
                    VALUES (%s, %s, %s, %s)
                """, (symbol, symbol_standardized, stock_name or '', industry or ''))
        
        conn.commit()
        return True
    except Exception as e:
        print(f"更新股票信息到数据库失败: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()
